package com.biz.hunter.dao;

import com.biz.hunter.db.entity.JobDesc;
import com.biz.hunter.db.entity.PlatformEnum;
import com.biz.hunter.dbutil.BaseDAO;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import java.util.Collections;
import java.util.List;
import java.util.Random;

@Repository
public class JobDAO {
    public static final int PAGE_SIZE = 8;
    private static Logger logger = LoggerFactory.getLogger(JobDAO.class);

    static final String SQL_SAVE = "insert into tb_jobdesc(jobcode,name,description,majorduty,"
            + "companyid,cityid,location,expiredDate," + "reward,domainid,employeenum,platformid,"
            + "annualpayment, createDate,status, roleid, expectcompanyid, expectcompanyid2, expectcompanyid3,comment,"
            + " createdby,expectJob, expectJobId,majorDomainId1,majorDomainId2,majorDomainId3,keywords) values(?,?,?,?,"
            + "?,?,?,?," + "?,?,?,?,?," + "?,?, ?, ?, ?, ?, ?, ?,?,?,?,?,?,?)";

    private static final String SQL_UPDATE = "update tb_jobdesc set jobcode=?, name=?"
            + ",description=?, majorduty=?,companyid=?,cityid =?,location=?,expiredDate=?,reward=? ,domainid=?, employeenum =?, "
            + " platformid=? ,annualpayment = ?, createDate=?, status=?, roleid=?, expectcompanyid=?, "
            + "expectcompanyid2=?, expectcompanyid3=?, comment=?, createdby=? ,"
            + "majorDomainId1=?, majorDomainId2=?, majorDomainId3=? ,expectJob=?, expectJobId=?, keywords=?" + " where id =?";

    private static final String SQL_GET_ALL = "select * from tb_jobdesc";
    private static final String SQL_GET_ALL_WITH_LIMIT = "select * from tb_jobdesc where 1=1 ";
    private static final String SQL_GET_ALL_COUNT = "SELECT COUNT(*) FROM tb_jobdesc where 1=1";
    private static final String SQL_GET = "select * from tb_jobdesc  where id = ?";
    private static final String SQL_DELETE = "delete from tb_jobdesc where id=?";
    private static final String SQL_GET_REWARD = "select * from tb_jobdesc where reward > 0";
    private static final String SQL_GET_REWARD_DOMAIN_PAGE = "select * from tb_jobdesc where reward > 0 and platformid=? and domainid =? limit ?,?";
    private static final String SQL_GET_KEYWORD_PAGE = "select * from tb_jobdesc where name like ? and platformid=? and domainid =? limit ?,?";
    private static final String SQL_GET_REWARD_PAGE = "select * from tb_jobdesc where reward > 0 and platformid=?  limit ?,?";
    private static final String SQL_GET_REWARD_PAGE2 = "select * from tb_jobdesc where status > 0 and (platformid=? or platformid =3) limit ?,?";
    private static final String SQL_GET_REWARD_PAGE3 = "select * from tb_jobdesc where "
            + "status > 0 and (platformid=? or platformid =  3) and ? < expireddate ";

    private static final String SQL_GET_KEYWORD = "select * from tb_jobdesc  where name like ?";
    private static final String SQL_GET_BY_JOBCODE = "select * from tb_jobdesc  where jobcode = ?";
    // TODO get by keywords
    private static final String SQL_GET_PAGELIST = "select * from tb_jobdesc  "
            + "where domainid = ? and platformid=? limit ?, ?";
    private static final String SQL_GET_ASSISTANT_PAGE = "select * from tb_jobdesc where  "
            + "platformid=? and domainid =? and name like ? and companyid in (?) limit ?,?";
    private static final String SQL_GET_ASSISTANT_PAGE2 = "select * from tb_jobdesc "
            + "where status > 0 and  (platformid=? or platformid=3) and ? < expireddate  "
            + "and domainid =? and name like ? and companyid in (?) limit ?,?";

    private static final String SQL_GET_JOB_CREATED_BY = "SELECT a.*,b.name as 'companyName' from tb_jobdesc as a left join tb_company as b on a.companyid = b.id WHERE a.createdby=? ";
    private static final String SQL_GET_JOB_CREATED_BY_STATUS = "SELECT a.*,b.name as 'companyName' from tb_jobdesc as a left join tb_company as b on a.companyid = b.id WHERE a.createdby=? and a.status = ? order by a.status desc ,a.createDate desc  limit ?, ?";

    private static final String SQL_UPDATE_HOLD = "UPDATE tb_jobdesc set status=0 WHERE id=?";
    private static final String SQL_UPDATE_PUBLISH = "UPDATE tb_jobdesc set status=1 WHERE id=?";

    private static final String SQL_GET_JOB_BY_NAME = "SELECT * FROM tb_jobdesc WHERE name = ?";

    private static final String SQL_JOB_CODE = "UPDATE seq SET val=LAST_INSERT_ID(val+1) WHERE name=?";
    private static final String SQL_JOB_CODE_2 = "SELECT LAST_INSERT_ID() AS id";

    private static final String SQL_GET_REWARD_DOMAIN_PAGE2 = "select * from tb_jobdesc where status > 0 "
            + "and (platformid=? or platformid=3) and domainid =? limit ?,?";
    private static final String SQL_GET_REWARD_DOMAIN_PAGE3 = "select * from tb_jobdesc where status > 0 "
            + "and (platformid=? or platformid=3) " + "and ? < expireddate and domainid =? limit ?,?";

    private static final String SQL_GET_REWARD_DOMAIN_PAGE4 = "select * from tb_jobdesc where status > 0 "
            + "and (platformid=? or platformid=3) " + "and ? < expireddate and  (roleid =? or roleid=?) and cityid = ? "
            + "and  ((expectcompanyid = ? or expectcompanyid2= ? or expectcompanyid3 =? or companyId in ( ? )) "
            + " or (domainid in (select id from tb_domain where parentid= ?) and companyId <> ? )) limit ?,?";

    public int save(JobDesc jd) {
        Long id = null;
        if (jd == null) {
            logger.error("the input jobdesc is null");
            return -1;
        }

        Long result = BaseDAO.save(SQL_SAVE, jd.getJobCode(), jd.getName(), jd.getDescription(), jd.getMajorDuty(),
                jd.getCompanyId(), jd.getCityId(), jd.getLocation(), jd.getExpiredDate(), jd.getReward(),
                jd.getDomainID(), jd.getEmployeeNum(), jd.getPlatformID(), jd.getAnnualPayment(), jd.getCreateDate(),
                jd.getStatus(), jd.getRoleid(), jd.getExpectcompanyid(), jd.getExpectcompanyid2(),
                jd.getExpectcompanyid3(), jd.getComment(), jd.getCreatedby(), jd.getExpectJob(), jd.getExpectJobId(),
                jd.getMajorDomainId1(), jd.getMajorDomainId2(), jd.getMajorDomainId3(), jd.getKeywords());
        if (result != null) {
            return result.intValue();
        }
        return -1;
    }

    public int delete(long jobid) {
        if (jobid <= 0) {
            return -1;
        }
        return BaseDAO.execute(SQL_DELETE, jobid);
    }

    public List<JobDesc> getALL() {
        return BaseDAO.queryForList(JobDesc.class, SQL_GET_ALL);
    }

    //
    // public Long getCountOfAll() {
    // return (Long) BaseDAO.queryOneRowOneCol(SQL_GET_ALL_COUNT);
    // }
    //
    // public List<JobDesc> getAllWithLimit(long start, long length) {
    // return BaseDAO.queryForList(JobDesc.class, SQL_GET_ALL_WITH_LIMIT, start,
    // length);
    // }

    public Long getCountOfAll(String name, Integer companyId, Integer status) {
        String sql = SQL_GET_ALL_COUNT;
        if (StringUtils.hasText(name)) {
            sql += " and name like '" + name + "'";
        }
        if (companyId != null) {
            sql += " and companyId =" + companyId + " ";
        }
        if (null != status) {
            sql += " and status = " + status + " ";
        }
        return (Long) BaseDAO.queryOneRowOneCol(sql);
    }

    public List<JobDesc> getAllWithLimit(String name, Integer companyId, Integer status, long start, long length) {
        String sql = SQL_GET_ALL_WITH_LIMIT;
        if (StringUtils.hasText(name)) {
            sql += " and name like '" + name + "'";
        }
        if (companyId != null) {
            sql += " and companyId =" + companyId + " ";
        }
        if (null != status) {
            sql += " and status =" + status + " ";
        }
        sql += " LIMIT ?, ?";
        return BaseDAO.queryForList(JobDesc.class, sql, start, length);
    }

    public List<JobDesc> getJobsWithReward() {

        return BaseDAO.queryForList(JobDesc.class, SQL_GET_REWARD);

    }

    public int update(JobDesc jd) {
        return BaseDAO.execute(SQL_UPDATE, jd.getJobCode(), jd.getName(), jd.getDescription(), jd.getMajorDuty(),
                jd.getCompanyId(), jd.getCityId(), jd.getLocation(), jd.getExpiredDate(), jd.getReward(),
                jd.getDomainID(), jd.getEmployeeNum(), jd.getPlatformID(), jd.getAnnualPayment(), jd.getCreateDate(),
                jd.getStatus(), jd.getRoleid(), jd.getExpectcompanyid(), jd.getExpectcompanyid2(),
                jd.getExpectcompanyid3(), jd.getComment(), jd.getCreatedby(), jd.getMajorDomainId1(),
                jd.getMajorDomainId2(), jd.getMajorDomainId3(), jd.getExpectJobName(), jd.getExpectJobId(), jd.getKeywords(), jd.getId());
    }

    public int holdJob(int jobid) {
        return BaseDAO.execute(SQL_UPDATE_HOLD, jobid);
    }

    public int publishJob(int jobid) {
        return BaseDAO.execute(SQL_UPDATE_PUBLISH, jobid);
    }

    public JobDesc getById(long jobId) {
        return BaseDAO.queryForObject(JobDesc.class, SQL_GET, jobId);
    }

    public JobDesc getByJobname(String jobname) {
        return BaseDAO.queryForObject(JobDesc.class, SQL_GET_JOB_BY_NAME, jobname);
    }

    public List<JobDesc> getJobDescCreatedBy(int createdby, int start, Integer status) {
        String sql = SQL_GET_JOB_CREATED_BY;
        if (status != null) {
            sql += " and a.status = ?";

        }
        sql += " order by a.status desc ,a.createDate desc  limit ?, ?";
        return BaseDAO.queryForList(JobDesc.class, sql, createdby, status, start, PAGE_SIZE);
    }

    public List<JobDesc> getJobDescList(String... keywords) {
        if (keywords == null) {
            return Collections.emptyList();
        }
        if (keywords.length == 1) {
            // Object[] para = new Object[]{"%"+ keywords+"%"};
            return BaseDAO.queryForList(JobDesc.class, SQL_GET_KEYWORD, "%" + keywords + "%");
        }
        // TODO more than one keywords
        return Collections.emptyList();

    }

    public List<JobDesc> getByName(String name) {
        // Object[] para = new Object[]{"%"+ name +"%"};
        return BaseDAO.queryForList(JobDesc.class, SQL_GET_KEYWORD, "%" + name + "%");
    }

    public List<JobDesc> getByJobCode(String code) {
        // Object[] para = new Object[]{"%"+ name +"%"};
        return BaseDAO.queryForList(JobDesc.class, SQL_GET_BY_JOBCODE, code);
    }

    public List<JobDesc> getJobsWithReward(int platformId) {
        return BaseDAO.queryForList(JobDesc.class, SQL_GET_REWARD, platformId);
    }

    public List<JobDesc> getJobsWithReward(String key, Integer platformId, Integer domainId, Integer offset,
                                           Integer end) {
        return BaseDAO.queryForList(JobDesc.class, SQL_GET_KEYWORD_PAGE, key, platformId, domainId, offset, end);
    }

    public List<JobDesc> getJobsWithKeyWord(String key, Integer platformId, Integer domainId, Integer offset,
                                            Integer end) {
        return BaseDAO.queryForList(JobDesc.class, SQL_GET_KEYWORD_PAGE, key, platformId, domainId, offset, end);
    }

    public List<JobDesc> getJobs4Candidate(Integer domainId, Integer offset, int pagesize) {
        logger.debug(" get job list domain id is " + domainId);
        logger.debug(" get job list offset  is " + offset);
        return BaseDAO.queryForList(JobDesc.class, SQL_GET_REWARD_DOMAIN_PAGE2, PlatformEnum.Candidate.getValue(),
                domainId, offset, pagesize);
    }

    public List<JobDesc> getJobs4Candidate2(Integer domainId, Integer offset, int pagesize, long today) {
        logger.debug(" get job list domain id is " + domainId);
        logger.debug(" get job list offset  is " + offset);
        return BaseDAO.queryForList(JobDesc.class, SQL_GET_REWARD_DOMAIN_PAGE3, PlatformEnum.Candidate.getValue(),
                today, domainId, offset, pagesize);
    }

    public List<JobDesc> searchJobs4Candidate(int domainId, String jobName, String conditionStr, Integer offset,
                                              int pageSize) {

        List<JobDesc> jobs = BaseDAO.queryForList(JobDesc.class, SQL_GET_ASSISTANT_PAGE,
                PlatformEnum.Candidate.getValue(), domainId, jobName, conditionStr, offset, pageSize);
        return jobs;
    }

    public List<JobDesc> searchJobs4Candidate2(int domainId, String jobName, String conditionStr, Integer offset,
                                               int pageSize, long today) {

        List<JobDesc> jobs = BaseDAO.queryForList(JobDesc.class, SQL_GET_ASSISTANT_PAGE2,
                PlatformEnum.Candidate.getValue(), today, domainId, jobName, conditionStr, offset, pageSize);
        return jobs;
    }

    public List<JobDesc> searchAssistantJobs4Hunter(int domainId, Integer offset, int pageSize) {
        return null;
    }

    public List<JobDesc> searchRecommendJobs4Hunter(Integer offset, int pageSize) {
        logger.debug(" get job list offset  is " + offset);
        return BaseDAO.queryForList(JobDesc.class, SQL_GET_REWARD_PAGE2, PlatformEnum.Hunting.getValue(), offset,
                pageSize);
    }

    public List<JobDesc> searchRecommendJobs4Hunter2(Integer offset, int pageSize, long today, List<Integer> domains) {
        String sql = SQL_GET_REWARD_PAGE3;
        StringBuilder sb = new StringBuilder();
        for (Integer domainId : domains) {
            sb.append(domainId).append(",");
        }
        String str = sb.toString();
        if (str.endsWith(",")) {
            str = str.substring(0, str.length() - 1);
            sql += " and domainid in ( " + str + ")";
        }
        sql += " order by id desc limit ?,?";
        logger.debug(" get job list offset  is " + offset);
        logger.info("query jobdesc sql:{}", sql);
        return BaseDAO.queryForList(JobDesc.class, sql, PlatformEnum.Hunting.getValue(), today, offset,
                pageSize);
    }

    private static final String COUNT_REWARD_JOBS = "select COUNT(*) from tb_jobdesc where "
            + "status > 0 and (platformid=? or platformid =3) and ? < expireddate ";

    public long countRecommendJobs4Hunter(long date, List<Integer> domains) {
        logger.debug(" count job for today ： " + date);
        String sql = COUNT_REWARD_JOBS;
        StringBuilder sb = new StringBuilder();
        for (Integer domainId : domains) {
            sb.append(domainId).append(",");
        }
        String str = sb.toString();
        if (str.endsWith(",")) {
            str = str.substring(0, str.length() - 1);
            sql += " and  domainid in (" + str + ")";
        }
        logger.info("sql===" + sql);
        Object size = BaseDAO.queryOneRowOneCol(sql, PlatformEnum.Hunting.getValue(), date);
        if (size == null) {
            return 0;
        }
        logger.debug("count jobs is " + size.toString());
        try {
            long total = (Long) size;
            logger.debug("count jobs size is convert to long " + total);
            return total;
        } catch (Exception ex) {
            ex.printStackTrace();
            return 0;
        }

    }

    public String getJobCodeString() {
        long res = BaseDAO.updateone(SQL_JOB_CODE, "jobcode");
        if (res > 0) {
            return String.valueOf(res);
        } else {
            Random rand = new Random();
            String code = Integer.toString(rand.nextInt(999));
            return "BAD-JOB-CODE" + code;
        }
    }

    public List<JobDesc> searchJobsByExpectJob(Integer domainId, Integer offset, int pagesize, long today) {
        logger.debug(" get job list domain id is " + domainId);
        logger.debug(" get job list offset  is " + offset);
        return BaseDAO.queryForList(JobDesc.class, SQL_GET_REWARD_DOMAIN_PAGE3, PlatformEnum.Candidate.getValue(),
                today, domainId, offset, pagesize);
    }

    public List<JobDesc> searchJobsByExpectJob_delete(Integer expectJob, long roleId, int companyId, String companys,
                                                      int domainId, Integer offset, int pageSize, long today, int cityId) {
        return BaseDAO.queryForList(JobDesc.class, SQL_GET_REWARD_DOMAIN_PAGE4, PlatformEnum.Candidate.getValue(),
                today, expectJob, roleId, cityId, companyId, companyId, companyId, companys, domainId, companyId,
                offset, pageSize);
    }

    private static final String SQL_GET_REWARD_DOMAIN_PAGE5 = "select * from (select * from tb_jobdesc "
            + "where status > 0 " + " and (platformid=? or platformid=3) "
            + " and ? < expireddate and  roleid  in (? ,?) and cityid = ? " + " and  companyId in ( ? ) " + " union "
            + " select * from tb_jobdesc where status > 0 " + " and (platformid=? or platformid=3) "
            + " and ? < expireddate and  roleid in(?,?) and cityid = ? "
            + " and domainid in (?,?,?)  and companyId <> ? ) jobs limit ?,?";

    public List<JobDesc> searchJobsByDomain(Integer expectJob, long roleId, int companyId, String companys,
                                            int majorDomainId1, int majorDomainId2, int majorDomainId3, long today, int cityId, Integer offset,
                                            int pageSize) {
        List<JobDesc> list = BaseDAO.queryForList(JobDesc.class, SQL_GET_REWARD_DOMAIN_PAGE5,
                PlatformEnum.Candidate.getValue(), today, expectJob, roleId, cityId, companys,
                PlatformEnum.Candidate.getValue(), today, expectJob, roleId, cityId, majorDomainId1, majorDomainId2,
                majorDomainId3, companyId, offset, pageSize);

        return list;
    }

    final String SQL_GET_COMPANYID = "select * from tb_jobdesc where companyId = ? limit ? ,?";

    public List<JobDesc> getAllperPage(int companyId, int offset, int pageSize) {

        List<JobDesc> list = BaseDAO.queryForList(JobDesc.class, SQL_GET_COMPANYID, companyId, offset, pageSize);

        return list;
    }

    final String SQL_GET_PARAMS = "select * from tb_jobdesc where  ? limit ? ,?";

    public List<JobDesc> filterJobsPerPage(String param, int offset, int pageSize) {

        List<JobDesc> list = BaseDAO.queryForList(JobDesc.class, SQL_GET_PARAMS, param, offset, pageSize);

        return list;
    }

    final String SQL_like_job = "insert into userJobs(openid,jobid) values(?,?)";

    public void like(String openid, int jobId) {
        Long result = BaseDAO.save(SQL_like_job, openid, jobId);
    }

    final String SQL_GET_FAVORJOBS = "select t.* from tb_jobdesc t,userJobs j where t.id = j.jobid and openid = ?";

    public List<JobDesc> searchUserJobs(String openid) {
        List<JobDesc> list = BaseDAO.queryForList(JobDesc.class, SQL_GET_FAVORJOBS, openid);

        return list;
    }


    public List<JobDesc> getJobDescCreatedByStatus(int createdby, int start, int status) {
        return BaseDAO.queryForList(JobDesc.class, SQL_GET_JOB_CREATED_BY_STATUS, createdby, status, start, PAGE_SIZE);
    }
}
